#! /bin/bash
export LANG=zh_CN.UTF-8
HIVE_HOME=/usr/bin/hive


${HIVE_HOME} -S -e "
--分区
SET hive.exec.dynamic.partition=true;
SET hive.exec.dynamic.partition.mode=nonstrict;
set hive.exec.max.dynamic.partitions.pernode=10000;
set hive.exec.max.dynamic.partitions=100000;
set hive.exec.max.created.files=150000;

------导入数据--------------
insert into table edu_dwb.dwb_customer_relationship_detail PARTITION (dt)
--客户意向宽表
select
    fcr.id,
    fcr.create_date_time,
    fcr.update_date_time,
    fcr.deleted,
    fcr.customer_id,
    fcr.first_id,
    fcr.origin_type,
    fcr.itcast_school_id,
    fcr.itcast_subject_id,
    fcr.creator,
    fcr.origin_channel,
    dc.id as stu_id,
    dc.area,
    dcc.id as clue_id,
    dcc.customer_relationship_id,
    dcc.clue_state,
    de.id as employee_id,
    de.tdepart_id,
    dsd.id as depart_id,
    dsd.name as depart_name,
    disu.id as sub_id,
    disu.name as sub_name,
    disc.id as school_id,
    disc.name as school_name,
    substring(fcr.create_date_time,1,10) as dt
from edu_dwd.dwd_fact_customer_relationship fcr
    LEFT JOIN edu_dwd.dwd_dim_customer dc
    on fcr.id = dc.customer_relationship_id and dc.end_date = '9999-99-99'
    left join edu_dwd.dwd_fact_customer_clue dcc
    on fcr.id = dcc.customer_relationship_id and dcc.end_date = '9999-99-99'
    left join edu_dwd.dwd_dim_employee de on fcr.creator = de.id
    left join edu_dwd.dwd_dim_scrm_department dsd on de.tdepart_id = dsd.id
    left join edu_dwd.dwd_dim_itcast_school disc on fcr.itcast_school_id = disc.id
    left join edu_dwd.dwd_dim_itcast_subject disu on fcr.itcast_subject_id = disu.id;

insert into table edu_dwb.dwb_customer_clue_detail PARTITION (dt)
--客户线索宽表
select
    fcc.id,
    fcc.create_date_time,
    fcc.update_date_time,
    fcc.deleted,
    fcc.customer_relationship_id,
    fcc.clue_state,
    dca.customer_relationship_first_id,
    dca.appeal_status,
    fcr.origin_channel,
    substring(fcc.create_date_time,1,10) as dt
from edu_dwd.dwd_fact_customer_clue fcc
    left join edu_dwd.dwd_dim_customer_appeal dca
    on fcc.customer_relationship_id = dca.customer_relationship_first_id and dca.end_date = '9999-99-99'
    left join edu_dwd.dwd_fact_customer_relationship fcr
    on fcc.customer_relationship_id = fcr.id and fcr.end_date = '9999-99-99';
"